# -*_ codeing=utf-8 -*-
# @Time: 2024/11/5 16:13
# @Author: foxhuty
# @File: gaokao2025_tool.py
# @Software: PyCharm
# @Based on python 3.13
import pandas as pd
from sympy import symbols, solve
import os
import time
import datetime
import re


class GaokaoData2025:
    """
    从2025年起四川采用新高考（3+1+2)模式。这是专门针对新高考模式的政治, 地理, 生物, 化学四门赋分学科而设计的程序，
    以帮助高中年级老师做成绩分析用。还可以计算各班平均分及占比，有效分统计等。
    该程序设计用于帮助高中教师分析新高考模式下的学生成绩，特别是政治、地理、生物和化学四门学科的赋分情况。
    程序可以读取Excel文件中的成绩数据，计算各学科的赋分等级和赋分值，统计班级平均分，各科有效分并生成包含这些分析结果的Excel文件。
    注意：python 3.13,pandas 2.4 版本测试正常。在 python 3.9,pandas 1.4版本测试中，把 map换成applymap后,程序可正常运行。
    """
    # 各个等级赋分区间（T1——T2）
    # A_T_range = [86, 100]
    # B_T_range = [71, 85]
    # C_T_range = [56, 70]
    # D_T_range = [41, 55]
    # E_T_range = [30, 40]
    A_T_range = [76, 90]
    B_T_range = [61, 75]
    C_T_range = [46, 60]
    D_T_range = [31, 45]
    E_T_range = [20, 30]

    # line_ratio = None
    physics_line_ratio = None
    history_line_ratio = None

    def __init__(self, file):
        if not os.path.isfile(file) or not file.endswith('.xlsx'):
            raise FileNotFoundError('文件路径无效')
        self.file = file
        try:
            excel_file = pd.ExcelFile(self.file)
        except FileNotFoundError as e:
            raise IOError(f'无法读取文件：{e}')
        self.sheet_names = [sheet_name for sheet_name in excel_file.sheet_names if
                            sheet_name in ['物理类', '历史类', '总表']]
        self.data_list = [pd.read_excel(self.file, sheet_name=sheet_name, dtype={'考号': str, '考生号': str}) for
                          sheet_name in
                          self.sheet_names]

    def __str__(self):
        return os.path.basename(self.file)

    def get_grade_data(self):
        """
        分别获取政治，地理，化学和生物学科的赋分等级和赋分值后生成excel数据表
        :return: None
        """
        data = self.data_list
        data_added = []
        for item in range(len(data)):
            subjects = [col for col in data[item].columns if col in ['政治', '地理', '生物', '化学']]
            print(f'{self.sheet_names[item]}各学科等级人数及卷面分值区间'.center(100, '*'))
            subject_added_data = []
            for subject in subjects:
                data_item = self.get_grade(data[item], subject)
                subject_added_data.append(data_item)
            subjects_added_data = subject_added_data[-1]
            # subjects_added_data = pd.concat(subject_added_data, ignore_index=False,join='inner', axis=1)
            subjects_added_data_cols = [col for col in subjects_added_data.columns if
                                        col in ['语文', '数学', '英语', '物理', '历史', '政治赋分', '地理赋分',
                                                '生物赋分', '化学赋分']]
            # 新版pandass要用astype(float).round(2)转换成2位小数。
            subjects_added_data['总分赋分'] = subjects_added_data[subjects_added_data_cols].sum(axis=1).astype(
                float).round(2)
            subjects_added_data.sort_values(by='总分赋分', ascending=False, inplace=True)
            subjects_added_data['序号'] = [i + 1 for i in range(len(subjects_added_data))]
            data_added.append(subjects_added_data)
        return data_added

    def get_grade(self, data, subject):
        """
        分别获取政治，地理，化学和生物学科的赋分等级和赋分值
        :param data: df数据表
        :param subject: 学科
        :return: 返回获取了赋分等级和赋分值的df数据
        """
        max_score, min_score = self.get_subject_max_min_score(data, subject)
        data[subject + '等级'] = data[subject].apply(lambda x: self.get_level(x, min_score))
        data[subject + '赋分'] = data[subject].apply(
            lambda x: self.get_final_scores(x, min_score, max_score))
        return data

    @staticmethod
    def separate_data(data_mixed):
        """
         用于分科后考试，赋分后再生成物理类和历史类两个df，用于生成excel文件
        :param data_mixed:
        :return:物理类和历史类两个df
        """

        history_min = data_mixed['历史'].min(skipna=True)
        physics_min = data_mixed['物理'].min(skipna=True)
        data_history = data_mixed[data_mixed['历史'] >= history_min]
        # 删除多列
        data_history = data_history.copy()
        data_history.drop(['物理', '化学', '化学等级', '化学赋分'], axis=1, inplace=True)

        data_physics = data_mixed[data_mixed['物理'] >= physics_min]
        # 删除单列
        data_physics = data_physics.copy()
        del data_physics['历史']
        return data_physics, data_history

    def subjects_average(self, data, subjects):
        """
         计算各学科的平均分。
        :param data: df数据
        :param subjects: 参加计算平均分的学科
        :return: 返回一个df。包含有各班各科平均分，年级平均分和各科平均分在年级平均分中的占比，用于制作折线图
        """
        class_av = data.groupby('班级')[subjects].mean().round(2)
        # class_av.fillna(0, inplace=True)
        # 求这个平均数，只能用apply(np.mean)才行。用其它的如mean(),apply(np.nanmean)都会报错
        # av_general = data[subjects].apply(np.mean, axis=0).round(2)
        av_general = data[subjects].mean().round(2)
        # av_general.fillna(0, inplace=True)
        av_percentage = class_av / av_general.round(2)
        # av_percentage.fillna(0, inplace=True)
        # print(av_percentage)

        # pandas 2.0以上用map替换applymap
        av_percentage = av_percentage.applymap(lambda x: format(x, '.2%'))  # 以百分号显示
        av_percentage = av_percentage.applymap(lambda x: x.replace('nan%', ''))  # 不显示nan%
        av_percentage.to_excel('av_percentage.xlsx')

        av_percentage_cols = [col + '占比' for col in av_percentage.columns]
        col_dict = dict(zip(av_percentage.columns, av_percentage_cols))
        av_percentage.rename(columns=col_dict, inplace=True)

        class_av.loc['年级平均'] = av_general
        class_av['参考人数'] = data['班级'].value_counts()
        class_av.loc['年级平均', '参考人数'] = class_av['参考人数'].sum()
        final_av_percentage = pd.concat([class_av, av_percentage], axis=1)
        final_av_percentage = self.change_columns_order(final_av_percentage)
        return final_av_percentage

    def get_average_school(self, data):
        """
         计算赋分后作有学科的平均分及平均分占比
        :param data: df
        :return: 返回一个有df元素的列表。
        """
        final_av = []
        subjects_av = [col for col in self.get_subjects(data) if col not in ['化学', '政治', '地理', '生物', '总分']]
        # print(subjects_av)

        final_av_percentage = self.subjects_average(data, subjects_av)
        final_av.append(final_av_percentage)
        return final_av

    @staticmethod
    def get_subjects(data):
        subjects = [col for col in data.columns if
                    col in ['语文', '数学', '英语', '物理', '历史',
                            '化学', '政治', '地理', '生物',
                            '化学赋分', '政治赋分', '地理赋分', '生物赋分', '总分', '总分赋分']]
        return subjects

    def excel_school_files(self):
        """
         学校组织的考试，要对化学政治地理和生物四科赋分。excel文件上要有一个总表工作表（sheet)
        """
        # line_percent = format(GaokaoData2025.line_ratio, '.0%')
        physics_line_percentage = format(GaokaoData2025.physics_line_ratio, '.0%')
        history_line_percentage = format(GaokaoData2025.history_line_ratio, '.0%')
        scores_added_data_list = self.get_grade_data()
        file_name = os.path.basename(
            self.file).split('.')[
                        0] + f'--物理类{physics_line_percentage}+历史类{history_line_percentage}划线成绩分析统计结果.xlsx'
        # file_name = self.filename_by_time()

        # with pd.ExcelWriter('D:\\成绩统计结果\\'+file_name) as writer:
        writer = pd.ExcelWriter('D:\\成绩统计结果\\' + file_name)
        for item in range(len(scores_added_data_list)):
            scores_added_data_list[item].to_excel(writer, sheet_name=f'{self.sheet_names[item]}-赋分表',
                                                  index=False,
                                                  float_format='%.2f')
            physics_data, history_data = self.separate_data(scores_added_data_list[item])
            physics_data.reset_index(drop=True, inplace=True)
            history_data.reset_index(drop=True, inplace=True)
            physics_data['排名'] = physics_data['总分赋分'].rank(method='min', ascending=False)
            physics_data['序号'] = [i + 1 for i in physics_data.index]
            history_data['排名'] = history_data['总分赋分'].rank(method='min', ascending=False)
            history_data['序号'] = [i + 1 for i in history_data.index]

            average_data_physics = self.get_average_school(physics_data)
            average_data_history = self.get_average_school(history_data)
            average_data_physics[item].to_excel(writer, sheet_name='物理类平均分', float_format='%.2f')
            average_data_history[item].to_excel(writer, sheet_name='历史类平均分', float_format='%.2f')

            good_scores_data_physics, unmatched_physics, assessment_physics = self.good_scores_school(
                physics_data, GaokaoData2025.physics_line_ratio)
            good_scores_data_history, unmatched_history, assessment_history = self.good_scores_school(
                history_data, GaokaoData2025.history_line_ratio)
            good_scores_data_history.to_excel(writer, sheet_name='历史类有效分')
            good_scores_data_physics.to_excel(writer, sheet_name='物理类有效分')

            physics_data.to_excel(writer, sheet_name=f'{self.sheet_names[item]}-物理类', index=False)
            history_data.to_excel(writer, sheet_name=f'{self.sheet_names[item]}-历史类', index=False)

            unmatched_physics.to_excel(writer, sheet_name='物理类错位生名单', index=False)
            unmatched_history.to_excel(writer, sheet_name='历史类错位生名单', index=False)

            assessment_physics.to_excel(writer, sheet_name='物理类学科评价')
            assessment_history.to_excel(writer, sheet_name='历史类学科评价')
            writer.close()
            return average_data_history[item], average_data_physics[
                item], good_scores_data_history, good_scores_data_physics, file_name

    def get_final_scores(self, score, min_score, max_score):
        """
        计算获取学生卷面分数的赋分值
        :param score:
        :param min_score:
        :param max_score:
        :return:
        """
        a_t = GaokaoData2025.A_T_range
        b_t = GaokaoData2025.B_T_range
        c_t = GaokaoData2025.C_T_range
        d_t = GaokaoData2025.D_T_range
        e_t = GaokaoData2025.E_T_range
        if score >= min_score[0]:
            score_added = self.get_added_score(score, min_score[0], max_score[0], a_t[0], a_t[1])
            return score_added
        elif score >= min_score[1]:
            score_added = self.get_added_score(score, min_score[1], max_score[1], b_t[0], b_t[1])
            return score_added
        elif score >= min_score[2]:
            score_added = self.get_added_score(score, min_score[2], max_score[2], c_t[0], c_t[1])
            return score_added
        elif score >= min_score[3]:
            score_added = self.get_added_score(score, min_score[3], max_score[3], d_t[0], d_t[1])
            return score_added
        elif score < min_score[3]:
            score_added = self.get_added_score(score, min_score[4], max_score[4], e_t[0], e_t[1])
            return score_added

    def good_scores_school(self, data, line_ratio):
        """
        用于学校考试的有效分统计，有效分数是由指定的划线分数计算得到
        :param data: df
        :param line_ratio:
        :return: 返回一个有效分统计的df
        """
        good_scores_data, unmatched_student_names, assessment_data = self.get_single_double_school_data(data,
                                                                                                        line_ratio)
        return good_scores_data, unmatched_student_names, assessment_data

    def get_single_double_school_data(self, data, line_ratio):
        """
         按照新高考的模式，计算中线和高线的有效分，完成单有效和双有效的统计
        :param data:
        :param line_ratio:
        :return: 返回含有有效分数，单有效和双有效的df

        """
        good_total_line = self.get_good_total_line(data, '总分赋分', line_ratio)
        subjects = [col for col in self.get_subjects(data) if col not in ['化学', '生物', '政治', '地理', '总分']]
        double_subjects_num_list = []
        single_data_list = []
        double_data_list = []
        subjects_scores = []
        unmatched_students_list = []
        unmatched_subjects_num_list = []
        for subject in subjects:
            double_subject_num_series = self.get_double_subject_num_series(data, subject, '总分赋分', good_total_line)
            subject_good_score = self.get_subject_good_score(data, subject, line_ratio)
            subjects_scores.append(subject_good_score)

            single_data = self.get_single_subject_data(data, subject, subject_good_score)
            double_data = self.get_double_subject_data(data, subject, '总分赋分', subject_good_score,
                                                       good_total_line)
            subject_unmatched_student = self.get_unmatched_subject_students(data, '总分赋分', subject,
                                                                            subject_good_score, good_total_line)
            unmatched_subject_num = subject_unmatched_student.groupby('班级')[subject].count()
            double_subjects_num_list.append(double_subject_num_series)
            unmatched_subjects_num_list.append(unmatched_subject_num)
            unmatched_students_list.append(subject_unmatched_student)
            single_data_list.append(single_data)
            double_data_list.append(double_data)
        # 计算选科后各班各科总分上线人数
        double_subjects_num_df = pd.concat(double_subjects_num_list, axis=1)
        self.add_total_num_row(double_subjects_num_df)

        # print(double_subjects_num_df)
        # 获取有效分数并转换成df
        good_scores_dict = dict(zip(subjects, subjects_scores))
        good_scores_df = pd.DataFrame(good_scores_dict, index=[0])
        # 合成单有效和双有效统计的df
        single_data = pd.concat(single_data_list, axis=1)
        double_data = pd.concat(double_data_list, axis=1)
        single_data.sort_index(inplace=True)
        double_data.sort_index(inplace=True)
        # 生成各科错位学生名单
        unmatched_students_by_class = pd.concat(unmatched_students_list, axis=1)
        unmatched_students_by_class.dropna(inplace=True, how='all', axis=1)
        # 计算错位人数，用于选科后统计错位生人数
        unmatched_subjects_num = pd.concat(unmatched_subjects_num_list, axis=1)
        self.add_total_num_row(unmatched_subjects_num)
        # unmatched_subjects_num.replace(0, np.nan, inplace=True)
        # 单有效统计：增加一列参考人数和一行年级共计
        single_data['参考人数'] = data['班级'].value_counts()
        self.add_total_num_row(single_data)

        # 增加一列上线率并用百分号显示
        single_data['上线率'] = single_data['总分赋分'] / single_data['参考人数']
        single_data['上线率'] = single_data['上线率'].apply(lambda x: format(x, '.2%'))  # 以百分号显示

        # 双有效统计：增加一列参考人数和一行年级共计
        double_data['参考人数'] = data['班级'].value_counts()
        self.add_total_num_row(double_data)
        # 计算学科评价，获取命中率，贡献率和评价等级
        assessment_df, shoot_df, contribution_df = self.get_assess_grade_df(single_data, double_data,
                                                                            double_subjects_num_df)
        subjects_assessment = [col for col in data.columns if
                               col in ['语文', '数学', '英语', '物理', '历史', '化学赋分', '生物赋分', '政治赋分',
                                       '地理赋分']]
        # 给各班各学科评价赋值：A，B，C，D，E，F（或无数据）
        for subject in subjects_assessment:
            self.grade_assessment(subject, shoot_df, contribution_df, single_data, assessment_df,
                                  double_subjects_num_df)
        # print(assessment_df)
        # 注意：新版用map，旧版用applymap
        shoot_df = shoot_df.applymap(lambda x: format(x, '.2%'))
        contribution_df = contribution_df.applymap(lambda x: format(x, '.2%'))
        shoot_contribution_assessment = pd.concat([shoot_df, contribution_df, assessment_df], axis=0,
                                                  keys=['命中率', '贡献率', '学科评价'])

        combined_data = pd.concat([good_scores_df, single_data, double_data, unmatched_subjects_num, assessment_df],
                                  keys=['有效分数', '单有效', '双有效', '错位人数', '学科评价'],
                                  axis=0)
        # 改变列顺序，把参考人数一列放在班级后面
        combined_data = self.change_columns_order(combined_data)

        return combined_data, unmatched_students_by_class, shoot_contribution_assessment

    @staticmethod
    def add_total_num_row(data):
        data.loc['年级共计'] = [data[col].sum() for col in data.columns]

    @staticmethod
    def get_double_subject_num_series(data, subject, total_col, total):
        """
        新高考中，各班选科人数不同，需要计算出每一个学科的总分上线的人数。
        """
        # 用reindex(class_names, fill_value=0)确保所有班级数据不会消失，没有值时用0代替。
        class_names = data['班级'].drop_duplicates().tolist()
        # 选出各班各个学科的总人数
        subject_data = data[data[subject] >= data[subject].min()]
        # 选出各班各个学科总分上线的人数的series
        double_subject_num_series = subject_data[subject_data[total_col] >= total].groupby('班级')[
            subject].count().reindex(class_names, fill_value=0)

        return double_subject_num_series

    @staticmethod
    def grade_assessment(subject, shoot_df, contribution_df, single_df, grade_df, double_subjects_df):
        """"
        学科等级评价。各班选科各不相同，如某班的一个学科单有效人数为零，则视为这个班没有人选学该学科，等级为无数据或用F表示。
        """

        for i in shoot_df.index:
            if single_df[subject].at[i] != 0:
                if (single_df[subject].at[i]) >= double_subjects_df[subject].at[i] * 0.8:
                    if (contribution_df[subject].at[i] >= 0.7) & (shoot_df[subject].at[i] >= 0.6):
                        grade_df[subject].at[i] = 'A'
                    elif (contribution_df[subject].at[i] >= 0.7) & (shoot_df[subject].at[i] < 0.6):
                        grade_df[subject].at[i] = 'B'
                    elif (contribution_df[subject].at[i] < 0.7) & (shoot_df[subject].at[i] >= 0.6):
                        grade_df[subject].at[i] = 'C'
                    else:
                        grade_df[subject].at[i] = 'D'
                else:
                    grade_df[subject].at[i] = 'E'
            else:
                grade_df[subject].at[i] = '无数据'

    @staticmethod
    def get_assess_grade_df(single, double, subjects_num_df):
        subjects = [subject for subject in single.columns if
                    subject in ['语文', '数学', '英语', '物理', '历史', '化学', '政治', '地理', '生物', '化学赋分',
                                '政治赋分', '地理赋分', '生物赋分']]
        single.fillna(0, inplace=True)
        double.fillna(0, inplace=True)
        subjects_num_df.fillna(0, inplace=True)
        shoot_dict_list = []
        contribution_dict_list = []
        for subject in subjects:
            shoot_subject = double[subject] / single[subject]
            shoot_dict_list.append(shoot_subject)
            # 计算贡献率，要用各班各学科的总分上线人数与该科的双有效人数相除。不能用各班的总分上线人线去除
            # subjects_num_df为所有学科总分上线人数的df(选学科目化学，政治，地理和生物的人数各班是不同的）
            contribution_subject = double[subject] / subjects_num_df[subject]
            contribution_dict_list.append(contribution_subject)
        shoot_dict = dict(zip(subjects, shoot_dict_list))
        shoot_df = pd.DataFrame(shoot_dict)
        contribution_dict = dict(zip(subjects, contribution_dict_list))
        contribution_df = pd.DataFrame(contribution_dict)
        shoot_df.fillna(0, inplace=True)
        contribution_df.fillna(0, inplace=True)
        grade_assess_df = pd.DataFrame(columns=contribution_df.columns, index=contribution_df.index)

        return grade_assess_df, shoot_df, contribution_df

    @staticmethod
    def get_unmatched_subject_students(data, total_col, subject, subject_score, total):
        """
        get the unmatched students in an exam
        :param data:
        :param subject:
        :param subject_score:
        :param total_col:
        :param total:
        :return:
        """
        unmatched_data = data[data[total_col] >= total]
        subject_unmatched_df = unmatched_data.loc[:, ['班级', '姓名', subject]].loc[
            unmatched_data[subject] < subject_score].sort_values(
            by=['班级', subject], ascending=[True, False]).reset_index(drop=True)
        return subject_unmatched_df

    @staticmethod
    def get_subject_max_min_score(data, subject):
        """
        用于计算获取各等级的卷面分区间（Y1-Y2)
        :param data:
        :param subject:
        :return: 各等级的卷面分区间（Y1-Y2)
        """
        max_score = []
        min_score = []
        subject_num = data[subject].count()
        A_num = int(subject_num * 0.15)
        B_num = int(subject_num * 0.35)
        C_num = int(subject_num * 0.35)
        D_num = int(subject_num * 0.13)
        data.sort_values(by=subject, ascending=False, inplace=True, ignore_index=True)
        # 计算A等级的卷面上下限分值及学生人数,由于每个等级的最低分会有不止一个，所以要再次切片
        data_subject_A = data.loc[:A_num - 1, subject]
        A_max = data_subject_A.max()
        A_min = data_subject_A.min()
        max_score.append(A_max)
        min_score.append(A_min)
        # 再次切片，确保A等级所有最低分都在同一个等级
        final_data_subject_A = data[data[subject] >= A_min]
        # 计算B等级的卷面上下限分值及学生人数
        data_subject_B = data.loc[len(final_data_subject_A):(len(final_data_subject_A) + B_num - 1), subject]
        B_max = data_subject_B.max()
        B_min = data_subject_B.min()
        max_score.append(B_max)
        min_score.append(B_min)
        # 再次切片，确保B等级所有最低分都在同一个等级
        final_data_subject_B = data[(data[subject] >= B_min) & (data[subject] <= B_max)]
        # 计算C等级的卷面上下限分值及学生人数
        data_subject_C = data.loc[(len(final_data_subject_A) + len(final_data_subject_B)):(
                len(final_data_subject_A) + len(final_data_subject_B) + C_num - 1), subject]
        C_max = data_subject_C.max()
        C_min = data_subject_C.min()
        max_score.append(C_max)
        min_score.append(C_min)
        # 再次切片，确保C等级所有最低分都在同一个等级
        final_data_subject_C = data[(data[subject] >= C_min) & (data[subject] <= C_max)]
        # 计算D等级的卷面上下限分值及学生人数
        data_subject_D = data.loc[(len(final_data_subject_A) + len(final_data_subject_B) + len(final_data_subject_C)):(
                len(final_data_subject_A) + len(final_data_subject_B) + len(final_data_subject_C) + D_num - 1), subject]
        d_max = data_subject_D.max()
        d_min = data_subject_D.min()
        max_score.append(d_max)
        min_score.append(d_min)
        # 再次切片，确保D等级所有最低分都在同一个等级
        final_data_subject_D = data[(data[subject] >= d_min) & (data[subject] <= d_max)]
        # 计算E等级的卷面上下限分值及学生人数
        final_data_subject_E = data.loc[
                               (len(final_data_subject_A) + len(final_data_subject_B) + len(final_data_subject_C) + len(
                                   final_data_subject_D)):subject_num - 1, subject]
        E_max = final_data_subject_E.max()
        E_min = final_data_subject_E.min()
        max_score.append(E_max)
        min_score.append(E_min)
        print(
            f'{subject}学科A等级人数为：{len(final_data_subject_A)},'
            f'B等级人数为：{len(final_data_subject_B)},'
            f'C等级人数为：{len(final_data_subject_C)},'
            f'D等级人数为：{len(final_data_subject_D)},'
            f'E等级人数为：{len(final_data_subject_E)}')
        # 新版pandas2.0以上，接收到的数字在列表中会显示成np.float64(81.0)，所以要用下标读取出来才能正常显示小数
        print(
            f'{subject}学科等级卷面Y1的值分别为：{min_score[0]}, {min_score[1]}, {min_score[2]}, {min_score[3]}, {min_score[4]}, '
            f'Y2的值分别为：{max_score[0]}, {max_score[1]}, {max_score[2]}, {max_score[3]}, {max_score[4]}')
        return max_score, min_score

    @staticmethod
    def get_single_subject_data(data, subject, subject_score):
        """
         计算获取一个学科单有效的df
        :param data: df
        :param subject: 学科
        :param subject_score: 一个学科的有效分数
        :return: 返回一个单有效的series
        """
        class_names = data['班级'].drop_duplicates().tolist()
        single_subject = data[data[subject] >= subject_score].groupby(['班级'])[
            subject].count().reindex(class_names, fill_value=0)
        return single_subject

    @staticmethod
    def get_double_subject_data(data, subject, total_col, subject_score, total):
        """
         计算获取学科双有校的df
        :param data: df
        :param subject: 学科
        :param total_col: 总分字段名
        :param subject_score: 学科有效分数
        :param total: 总分
        :return: 返回一个学科双有效的series
        """
        class_names = data['班级'].drop_duplicates().tolist()
        double_subject_data = data[data[total_col] >= total]
        double_subject = double_subject_data[double_subject_data[subject] >= subject_score].groupby('班级')[
            subject].count().reindex(class_names, fill_value=0)
        return double_subject

    @staticmethod
    def get_added_score(y, y1, y2, t1, t2):
        """
        计算赋分值的公式：高考赋分方法，其中y为原始卷面得分，t为赋值得分，
        t1和t2为所在等级赋值区间的下限和上限，y1和y2为卷面所在等级分数区间的下限和上限。
        :param y:
        :param y1:
        :param y2:
        :param t1:
        :param t2:
        :return: 一个学生的赋值得分（四舍五入取整）
        """
        t = symbols('t')
        try:
            if y == y1:
                scores_added = t1
                return scores_added
            else:
                scores_added = solve((t2 - t) / (t - t1) - (y2 - y) / (y - y1), t)
                return round(scores_added[0])
        except Exception as e:
            raise f'赋值时发生错误{e}'

    @staticmethod
    def get_level(score, min_score):
        """
         计算赋分学科的等级
        :param score: 学科的卷面得分
        :param min_score: 所在等级的最低分
        :return: 相应的等级
        """
        if score >= min_score[0]:
            return 'A'
        elif score >= min_score[1]:
            return 'B'
        elif score >= min_score[2]:
            return 'C'
        elif score >= min_score[3]:
            return 'D'
        elif score < min_score[3]:
            return 'E'

    @staticmethod
    def change_columns_order(data):
        col_first = '参考人数'
        cols_left = [col for col in data.columns if col != col_first]
        new_columns_data = data[[col_first] + cols_left]
        return new_columns_data

    @staticmethod
    def get_subject_good_score(data, subject, line_ratio):
        """
         计算学科有效分。按照总分上线率，计算学科有效分
        :param data: DataFrame
        :param subject: 学科
        :param line_ratio: 总分（分为高线和中线）
        :return: 学科的有效分（高线和低线）
        """
        # total_num = data.shape[0]
        # good_total_num = data.loc[data[total_col] >= GaokaoData2025.total_line].shape[0]
        # good_percent_ratio = good_total_num / total_num
        good_subject_num = int(data[subject].count() * line_ratio)
        data.sort_values(by=subject, ascending=False, inplace=True, ignore_index=True)
        good_subject_score = data.loc[:good_subject_num - 1, subject].min()

        return good_subject_score

    @staticmethod
    def get_good_total_line(data, total_col, line_ratio):
        """
        搂指定的比例计算划线线分数
        :param data:
        :param total_col:
        :param line_ratio
        :return:
        """
        data.sort_values(by=total_col, ascending=False, inplace=True, ignore_index=True)
        # total_num = data.shape[0]
        good_total_num = int(data[total_col].count() * line_ratio)
        good_total_line = data.loc[:good_total_num - 1, total_col].min()
        return good_total_line

    @staticmethod
    def filename_by_time():
        file_time = time.time()
        file_name = f'{file_time}.xlsx'
        return file_name

    @staticmethod
    def rename_columns(col_name, word):
        if word in col_name:
            return col_name.replace(word, '')
        else:
            return col_name

    @staticmethod
    def time_use(fn):
        def inner(*args, **kwargs):
            print(f'开始运行主函数{fn.__name__}'.center(106, '_'))
            start_time = time.time()
            result = fn(*args, **kwargs)
            end_time = time.time()
            time_used = end_time - start_time
            print(f'主函数{fn.__name__}结束运行'.center(106, '_'))
            print(f'运行主函数{fn.__name__}共计耗时{round(time_used, 2)}秒')
            return result

        return inner


class GetInfoFromId(object):
    def __init__(self, filename):
        self.filename = filename

    def make_regions_dict(self, regionNo, region):
        """
        生成身份证号前6位地区字典
        :param regionNo:
        :param region:
        :return:
        """
        filename = r'./static/id_region_2024.xlsx'
        data = pd.read_excel(filename, sheet_name=0, dtype={'cityNo': str,
                                                            'districtNo': str,
                                                            'provinceNo': str})
        code_list = data[regionNo].tolist()
        district_list = data[region].tolist()
        region_dict = dict(zip(code_list, district_list))
        return region_dict

    def open_file(self):
        data = pd.read_excel(self.filename, dtype={'身份证号': str}, sheet_name=0)
        return data

    def data_remove_string(self, data, remove_string, data_column='身份证号'):
        """
        去除身份证号的多余字符,主要用于把学籍号转换成身份证号
        :param data:
        :param data_column: 在excel表上新建身份证号一列，把学籍号一列复制到这一列。
        :param remove_string: 要去除的字符。
        :return: 返回整理好的DataFrame数据
        """
        data[data_column] = data[data_column].str.replace(remove_string, '')
        return data

    # 清洗身份证号数据格式
    def get_clean_id(self):
        data = self.open_file()
        re_pattern = r'^([1-9]\d{5}[12]\d{3}(0[1-9]|1[012])(0[1-9]|[12][0-9]|3[01])\d{3}[0-9xX])$'

        # data = data.loc[data['身份证号'].notna()].reset_index()
        bad_df = pd.DataFrame(columns=data.columns)

        id_nums = []
        id_nums_bad = []
        for i in data.index:
            data['身份证号'] = data['身份证号'].astype(str)
            id_num = data['身份证号'].at[i].strip('\t').strip()
            if re.match(re_pattern, id_num):
                id_nums.append(id_num)
            else:
                id_nums_bad.append(id_num)
                bad_df.loc[i] = data.loc[i]
                data.drop(index=[i], inplace=True)
        data['身份证号'] = id_nums
        bad_df['身份证号'] = id_nums_bad
        data.reset_index(inplace=True, drop=True)
        if len(id_nums_bad) != 0:
            # print(f'错误的身份证号：{id_nums_bad},共计有{len(id_nums_bad)}个错误。请修正后再次运行。')
            bad_df = bad_df.loc[:, ['姓名', '身份证号']]
            # print(bad_df)
        # else:
        #     print("太棒了！经电脑检测，所有身份证号无误。信息提取完整！谢谢使用！")
        return data, bad_df, id_nums_bad

    def get_sex_birth_age(self):
        data, bad_df, id_bad = self.get_clean_id()
        regions = ['华北', '东北', '华南', '中南', '西南', '西北', '港澳台']
        province_dict = {
            '11': '北京市', '12': '天津市', '13': '河北省', '14': '山西省', '15': '内蒙古自治区',
            '21': '辽宁省', '22': '吉林省', '23': '黑龙江省',
            '31': '上海市', '32': '江苏省', '33': '浙江省', '34': '安徽省', '35': '福建省', '36': '江西省',
            '37': '山东省',
            '41': '河南省', '42': '湖北省', '43': '湖南省', '44': '广东省', '45': '广西壮族自治区', '46': '海南省',
            '51': '四川省', '52': '贵州省', '53': '云南省', '54': '西藏自治区', '50': '重庆市',
            '61': '陕西省', '62': '甘肃省', '63': '青海省', '64': '宁夏回族自治区', '65': '新疆维吾尔自治区',
            '83': '台湾地区', '81': '香港特别行政区', '82': '澳门特别行政区'
        }
        district_dict = self.make_regions_dict('districtNo', 'city_district')
        birth_date = []
        ages = []
        data['性别'] = ['女' if int(data['身份证号'].at[i][-2]) % 2 == 0 else '男' for i in data.index]
        region_num = [data['身份证号'].at[i][0] for i in data.index]
        province_num = [data['身份证号'].at[i][0:2] for i in data.index]
        district_num = [data['身份证号'].at[i][0:6] for i in data.index]
        df_year = [data['身份证号'].at[i][6:10] for i in data.index]
        df_month = [data['身份证号'].at[i][10:12] for i in data.index]
        df_day = [data['身份证号'].at[i][12:14] for i in data.index]
        data['所在区域'] = None
        for i in data.index:
            birth = datetime.date(int(df_year[i]), int(df_month[i]), int(df_day[i]))
            birth = birth.strftime('%Y年%m月%d日')
            age = datetime.datetime.today().year - int(df_year[i])
            ages.append(age)
            birth_date.append(birth)
        data['出生日期'] = birth_date
        data['年龄'] = ages
        region = [regions[int(num) - 1] for num in region_num]
        province = [province_dict.get(num, 'nodata') for num in province_num]
        district = [district_dict.get(num, 'nodata') for num in district_num]
        data['所在区域'] = region
        data['所属省份'] = province
        data['地区'] = district
        # print(data['地区'])
        if '序号' not in data.columns.tolist():
            data.insert(loc=0, column='序号', value=1)
        data['序号'] = [str(i + 1) for i in data.index]
        filename = GaokaoData2025.filename_by_time()
        data.to_excel('D:\\成绩统计结果\\' + filename, index=False)
        show_data = data.loc[:,
                    ['序号', '姓名', '身份证号', '性别', '出生日期', '年龄', '所属省份', '地区', '所在区域']]
        return filename, bad_df, id_bad, show_data
